/*********************************************************************************
 *      Copyright:  (C) 2025 LingYun<iot25@lingyun>
 *                  All rights reserved.
 *
 *       Filename:  sqlite.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(12/04/25)
 *         Author:  LingYun <iot25@lingyun>
 *      ChangeLog:  1, Release initial version on "12/04/25 13:22:32"
 *                 
 ********************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>

#include "logger.h"
#include "pack_data.h"

#define DEBUG_OUT 0
#if		DEBUG_OUT

#define DEBUG_PRINT(fmt, args...)	printf(fmt, ##args)

#else

#define DEBUG_PRINT(fmt, args...)	do{} while(0)

#endif

static sqlite3		*s_db;

/* 初始化数据库 */
int db_init(const char *db_name)
{
	char		 create_table[256] = {0};	//create table statement
	char		*table_name = "TEMP";		//table name
	char		*zErrMsg = NULL;			//Error messages
	int		 	 rv = -1;					//create table falg
		
	if ( NULL == db_name )
	{
		log_error("NULL pointer");
		return -1;
	}

	/* 打开/创建数据库 */
	rv = sqlite3_open(db_name, &s_db);
	if( rv )
	{
		log_error("open database [%s] failure: %s", db_name, sqlite3_errmsg(s_db));
		return -1;
	}
	log_debug("open database [%s] successfully", db_name);

	/* 创建表 */
	snprintf(create_table, sizeof(create_table), "CREATE TABLE IF NOT EXISTS %s(ID TEXT NOT NULL,TIME TEXT PRIMARY KEY NOT NULL,TEMP FLOAT NOT NULL);", table_name);
	rv = sqlite3_exec(s_db, create_table, NULL, NULL, &zErrMsg);
	if( rv != SQLITE_OK )
	{
		log_debug("create table [TEMP] failure: %s", zErrMsg);
		sqlite3_free(zErrMsg);
		sqlite3_close(s_db);
		s_db = NULL;
		return -1;
	}
	log_debug("create table [%s] successfully\n",table_name);
	return 0;
}

/* 插入数据 */
int db_insert(const packet_t *pack)
{
    const char *sql = "INSERT INTO TEMP(ID, TIME, TEMP) VALUES(?, ?, ?);";
    sqlite3_stmt *stmt = NULL;
    int rv = -1;

    if (NULL == pack || NULL == s_db)
    {
        log_error("NULL pointer");
        return -1;
    }

    rv = sqlite3_prepare_v2(s_db, sql, -1, &stmt, NULL);
    if (rv != SQLITE_OK)
    {
        log_error("prepare insert statement failure: %s", sqlite3_errmsg(s_db));
        return -1;
    }
	 printf("即将插入数据: ID = %s, TIME = %s, TEMP = %.2f\n", pack->dev_id, pack->dev_time, pack->dev_temp); 
    /* 绑定参数 */
    sqlite3_bind_text(stmt, 1, pack->dev_id, -1, SQLITE_STATIC);
    sqlite3_bind_text(stmt, 2, pack->dev_time, -1, SQLITE_STATIC);
    sqlite3_bind_double(stmt, 3, pack->dev_temp);

    rv = sqlite3_step(stmt);
    sqlite3_finalize(stmt);

    if (rv != SQLITE_DONE)
    {
        log_error("insert records failure: %s", sqlite3_errmsg(s_db));
        return -1;
    }
	printf("数据插入成功\n");
    log_debug("insert records successfully");
    return 0;
}


/* 查询数据 */
int db_query(packet_t *pack)
{
    const char *sql = "SELECT ID, TIME, TEMP FROM TEMP LIMIT 1;";
    sqlite3_stmt *stmt = NULL;
    int rv = -1;

    if (NULL == pack || NULL == s_db)
    {
        log_error("NULL pointer");
        return -1;
    }

    if (sqlite3_errcode(s_db) == SQLITE_NOTADB)
    {
        return -1;
    }

    rv = sqlite3_prepare_v2(s_db, sql, -1, &stmt, NULL);
    if (rv != SQLITE_OK)
    {
        log_error("prepare query statement failure: %s", sqlite3_errmsg(s_db));
        return -1;
    }

    memset(pack, 0, sizeof(*pack));
    rv = sqlite3_step(stmt);
    
    if (rv == SQLITE_ROW)
    {
        /* 数据库有数据 */
        const unsigned char *id = sqlite3_column_text(stmt, 0);
        const unsigned char *time = sqlite3_column_text(stmt, 1);
        
        if (id) snprintf(pack->dev_id, sizeof(pack->dev_id), "%s", id);
        if (time) snprintf(pack->dev_time, sizeof(pack->dev_time), "%s", time);
        pack->dev_temp = sqlite3_column_double(stmt, 2);
        
        sqlite3_finalize(stmt);
        log_debug("query records successfully");
        return 0;
    }
    else if (rv == SQLITE_DONE)
    {
        /* 数据库空 */
        sqlite3_finalize(stmt);
        return -2;
    }
    else
    {
        /* 查询出错 */
        log_error("query records failure: %s", sqlite3_errmsg(s_db));
        sqlite3_finalize(stmt);
        return -1;
    }
}


/* 删除数据 */
int db_delete(void)
{
    const char *sql = "DELETE FROM TEMP WHERE TIME IN (SELECT TIME FROM TEMP LIMIT 1);";
    char *zErrMsg = NULL;
    int rv = -1;

    if (NULL == s_db)
    {
        log_error("database not initialized");
        return -1;
    }

    rv = sqlite3_exec(s_db, sql, NULL, NULL, &zErrMsg);
    if (rv != SQLITE_OK)
    {
        log_error("delete records failure: %s", zErrMsg);
        sqlite3_free(zErrMsg);
        return -1;
    }

    log_debug("delete records successfully");
    return 0;
}

/* 关闭数据库 */
int db_close(void)
{
    int rv = -1;

    if (s_db)
    {
        rv = sqlite3_close(s_db);
        if (rv != SQLITE_OK)
        {
            log_error("close database failure: %s", sqlite3_errmsg(s_db));
            return -1;
        }
        s_db = NULL;
        log_debug("close database successfully");
    }

    return 0;
}

